﻿/*
	Date	: 10.10.11
	Author	: Lee
	Purpose	: Cancel seat functionality
	
 */
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace GoldAir_DesktopApp
{
    public partial class frmCancel : Form
    {
        public frmCancel()
        {
            InitializeComponent();
        }
        //Lookup flight function
        private static DataTable LookupFlight(int UserId, int FlightNumber)
        {

            const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
            const string query = "Select route.from,route.destination,flight.departure,user_flight.num_seat,user_flight.checked_in,user_flight.checkin_datetime,user_flight.payment,user_flight.payment_date,user_flight.confirmed  from user_flight join flight on user_flight.flight_id=flight.id join route on flight.route_id=route.id Where user_id = @UserId and flight_id = @FlightNumber";

            MySqlConnection connection = new MySqlConnection();
            MySqlDataAdapter data = new MySqlDataAdapter();

            connection.ConnectionString = connStr;
            connection.Open();
            MySqlCommand command = connection.CreateCommand();


            DataTable result = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.Add("@UserId", MySqlDbType.Int32).Value = UserId;
                    cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = FlightNumber;

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
                conn.Close();
            }

            connection.Close();

            return result;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtFlightNumber.Text != "" && txtUserId.Text != "")
            {

                using (DataTable dt = LookupFlight(Int32.Parse(txtUserId.Text), Int32.Parse(txtFlightNumber.Text)))
                {
                    if (dt.Rows.Count == 0)
                    {
                        txtUserId.Focus();
                        MessageBox.Show("Not Found.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);

                        ClearForm();
                        return;
                    }
                    else
                    {
                        //assign the data to the controls

                        txtFrom.Text = Convert.ToString(dt.Rows[0][0]);  //from
                        txtTo.Text = Convert.ToString(dt.Rows[0][1]); //to
                        txtFlightTime.Text = Convert.ToString(dt.Rows[0][2]); //flight time
                        txtSeatNum.Text = Convert.ToString(dt.Rows[0][3]); //seat n
                        
                        if (Convert.ToString(dt.Rows[0][6])=="1")
                        {
                            txtPayment.Text = "YES";
                        }
                         else
                         {
                             txtPayment.Text = "NO";
                         }
                        
                        txtPaymentDate.Text = Convert.ToString(dt.Rows[0][7]); //

                        switch(Convert.ToString(dt.Rows[0][8]))
                        {
                            case "0":
                            txtStatus.Text = "UNCHECKED";
                            break;
                            case "1":
                            txtStatus.Text = "CHECKED IN";
                            break;
                            case "2":
                            txtStatus.Text = "PAYD";
                            break;
                            
                        }
                       

                    }
                }
            }
            else
            {
                MessageBox.Show("Enter User Id and Flight Id", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                ClearForm();
            }

        }


        private void ClearForm()
        {

            txtFrom.Text = "";
            txtTo.Text = "";
            txtFlightTime.Text = "";
            txtSeatNum.Text = "";
            txtPayment.Text = "";
            txtPaymentDate.Text = "";
            txtStatus.Text = "";

            txtUserId.Focus();

        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (txtFrom.Text != "")
            {

                Boolean CheckIn = false;
                DateTime CheckDateTime = DateTime.MinValue;
                Int16 Confirmed = 0;
                Int16 Seat = 0;
                

                const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
                const string query = "Update user_flight set checked_in=@CheckIn, checkin_datetime=@CheckDateTime, confirmed=@Confirmed, num_seat= @Seat Where user_id = @UserId and flight_id = @FlightNumber";

                MySqlConnection connection = new MySqlConnection();
                MySqlDataAdapter data = new MySqlDataAdapter();

                connection.ConnectionString = connStr;
                connection.Open();
                MySqlCommand command = connection.CreateCommand();

                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(query, conn))
                    {
                        cmd.Parameters.Add("@UserId", MySqlDbType.Int32).Value = txtUserId.Text;
                        cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = txtFlightNumber.Text;
                        cmd.Parameters.Add("@CheckIn", MySqlDbType.Int16).Value = CheckIn;
                        cmd.Parameters.Add("@CheckDateTime", MySqlDbType.DateTime).Value = CheckDateTime;
                        cmd.Parameters.Add("@Confirmed", MySqlDbType.Int16).Value = Confirmed;
                        cmd.Parameters.Add("@Seat", MySqlDbType.Int16).Value = Seat;

                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                MessageBox.Show("Seat Canceled!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                connection.Close();

                btnSearch.PerformClick();
            }
            else
            {
                MessageBox.Show("No Data Found", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }








    }
}
